#coding:utf-8
'''
Created on 2017-4-24

@author: sandy
'''

import MySQLdb
import threading
import logging
import time
from twisted.internet import reactor

'''
At this point your query has been executed and you need to get the results.
You have two options:
r=db.store_result()
# ...or...
r=db.use_result()
Both methods return a result object. What's the difference? 
store_result() returns the entire result set to the client immediately.
If your result set is really large, this could be a problem. 
One way around this is to add a LIMIT clause to your query, 
to limit the number of rows returned.
The other is to use use_result(), which keeps the result set in the server
and sends it row-by-row when you fetch.
This does, however, tie up server resources, and it ties up the connection: 
You cannot do any more queries until you have fetched all the rows.
Generally I recommend using store_result() unless your result set is really
huge and you can't use LIMIT for some reason.
'''

class DBPError(Exception):
    "Database pool Error"

class DBCommand(object):
    
    def __init__(self):
        pass
    
    def selectCon(self, linkcount):
        return 0
    
    def execute(self, con):
        pass
    
    def finish(self, error):
        pass
    
class KeepAliveCmd(DBCommand):

    def selectCon(self, linkcount):
        return 0
    
    def execute(self, con):
        c=con.cursor()
        sql = u"select 1+1"
        c.execute(sql)
        rows = c.fetchall()
        c.close()
        #logging.debug(u"KeepAliveCmd ret=%s", rows[0][0])

    def finish(self, error):
        pass
           

class QueryCmd(DBCommand):
    def __init__(self, conindex, ctx, sql, params, func):
        DBCommand.__init__(self)
        self.conindex = conindex
        self.ctx = ctx
        self.sql = sql
        self.params = params
        self.func = func
        self.rows = None
    
    def selectCon(self, linkcount):
        return self.conindex
    
    def execute(self, con):
        c=con.cursor()
        c.execute(self.sql, self.params)
        self.rows = c.fetchall()
        c.close()
        
    def finish(self, error):
        try:
            if error:
                self.func(self.ctx, error, None)
            else:
                self.func(self.ctx, None, self.rows)
        except Exception:
            logging.exception(u"QueryCmd.finish(),sql=%s,params=%s",self.sql, unicode(self.params))            
        finally:
            self.ctx = None
            self.sql = None
            self.func = None
            self.rows = None
            
class ExecuteCmd(DBCommand):
    def __init__(self, conindex, ctx, sql, params, func):
        DBCommand.__init__(self)
        self.conindex = conindex
        self.ctx = ctx
        self.sql = sql
        self.params = params
        self.func = func
        self.rowcount = None
    
    def selectCon(self, linkcount):
        return self.conindex
    
    def execute(self, con):
        c=con.cursor()
        c.execute(self.sql, self.params)
        self.rowcount = c.rowcount
        c.close()
        
    def finish(self, error):
        try:
            if error:
                self.func(self.ctx, error, 0)
            else:
                self.func(self.ctx, None, self.rowcount)
        except Exception:
            logging.exception(u"ExecuteCmd.finish(),sql=%s,params=%s",self.sql, unicode(self.params))   
        finally:                 
            self.ctx = None
            self.sql = None
            self.func = None 
            
class CallProcCmd(DBCommand):
    def __init__(self, conindex, ctx, proc, params, func):
        DBCommand.__init__(self)
        self.conindex = conindex
        self.ctx = ctx
        self.proc = proc
        self.params = params
        self.func = func
        self.sets = []
    
    def selectCon(self, linkcount):
        return self.conindex
    
    def execute(self, con):
        c=con.cursor()
        c.callproc(self.proc, self.params)
        while True:
            rows = c.fetchall()
            if not c.nextset():
                break
            else:
                self.sets.append(rows)
        c.close()
        
    def finish(self, error):
        try:
            if error:
                self.func(self.ctx, error, None)
            else:
                self.func(self.ctx, None, self.sets)
        except Exception:
            logging.exception(u"CallProcCmd.finish()")            
        finally:
            self.ctx = None
            self.proc = None
            self.func = None
            self.sets = None              

class DBConnection(object):
    
    def __init__(self, index, queuesize):
        self.index = index
        self.queuesize = queuesize
        self.db = ""
        self.user = ""
        self.passwd = ""
        self.host = ""
        self.port = 3306
        self.charset = ""        
        self.con = None
        self.toExit = False
        self.thread = None
        self.event = None
        self.cmdlist = []
        self.lock = None
        
    def start(self, db, user, passwd, host, port, charset):
        self.db = db
        self.user = user
        self.passwd = passwd
        self.host = host
        self.port = port
        self.charset = charset                
        
        self.event = threading.Event()
        self.lock = threading.Lock()
        self.thread = threading.Thread(target=self.svc)
        self.thread.start()
    
    def stop(self):
        self.toExit = True
        self.thread.join()
        self.thread = None
        self.event = None
        self.lock = None
    
    def getCharSet(self):
        return self.charset
    
    #MySQLdb目前不支持参数化语句，小心SQL注入攻击
    def escapeString(self, strt):
        return self.con.escape_string(strt)
        
    def putCmd(self, cmd):
        self.lock.acquire()
        if len(self.cmdlist)>self.queuesize:
            self.lock.release()
            reactor.callFromThread(cmd.finish, DBPError(u"mysqldb queue full"))
            return
        self.cmdlist.append(cmd)
        self.lock.release()
        self.event.set()
        
    def svc(self):
        lastexecutetime = time.time()
        while not self.toExit:
            if not self.con:
                self.connect()
                if not self.con:
                    i = 0
                    while i<50 and not self.toExit:
                        time.sleep(0.1)
                        i = i + 1
                    continue
                            
            if not self.event.wait(timeout=1):
                if time.time()-lastexecutetime>10*60:
                    self.putCmd(KeepAliveCmd())
                continue
            
            cmd = None
            self.lock.acquire()
            if len(self.cmdlist)==0:
                self.lock.release()
                self.event.clear()
                continue
            cmd = self.cmdlist.pop(0)
            self.lock.release()
            
            lastexecutetime = time.time()
            try:
                cmd.execute(self.con)
            except Exception, e:
                reactor.callFromThread(cmd.finish, e)
                logging.exception(u"cmd.execute(),sql=%s,params=%s",cmd.sql, unicode(cmd.params))
                self.checkConnect()
                continue
            
            reactor.callFromThread(cmd.finish, None)
        if self.con:
            self.con.close()
            self.con = None
        logging.warn(u"DBConnection thread exit!!!")    
    
    def checkConnect(self):
        try:
            self.con.ping()
        except Exception, e:
            logging.exception(u"self.con.ping()")
            logging.error(u"__con.ping() exception, error=%s", e)
            self.con.close()
            self.con = None
        
    def connect(self):
        try:
            self.con = MySQLdb.connect(db = self.db,
                                         user = self.user,
                                         passwd = self.passwd,
                                         host = self.host,
                                         port = self.port,
                                         charset = self.charset,
                                         connect_timeout = 5,
                                         read_timeout = 60,
                                         use_unicode = True
                                         )
        except Exception:
            self.con = None
            logging.exception(u"self.connect()")
        if self.con:
            #MySQLdb默认autocommit是OFF
            self.con.autocommit(True)
            self.con.charset = self.charset
            logging.warn(u"mysql %s:%d connected", self.host, self.port)

class DBConnectionPool(object):
    
    def __init__(self, db, user, passwd, host=u'127.0.0.1', port=3306, charset=u'utf8', linkcount=3, queuesize=512):
        self.db = db
        self.user = user
        self.passwd = passwd
        self.host = host
        self.port = port
        self.charset = charset
        self.linkcount = linkcount
        self.queuesize = queuesize
        
        self.cons = []
        for i in range(linkcount):#线程池
            self.cons.append(DBConnection(i, self.queuesize))
    
    def start(self):
        for con in self.cons:
            con.start(self.db,
                      self.user,
                      self.passwd,
                      self.host,
                      self.port,
                      self.charset)
    def stop(self):
        for con in self.cons:
            con.stop()
        self.cons = {}
    
    def getLinkCount(self):
        return self.linkcount
    
    def getCharset(self):
        return self.charset
            
    def putCmd(self, cmd):
        self.cons[cmd.selectCon(self.linkcount)].putCmd(cmd)
    
    def escapeString(self, srt):
        return self.cons[0].escapeString(srt)
    
    def query(self, conindex, ctx, sql, params, func):
        self.putCmd(QueryCmd(conindex, ctx, sql, params, func))

    def execute(self, conindex, ctx, sql, params, func):
        self.putCmd(ExecuteCmd(conindex, ctx, sql, params, func))
        
    def call(self, conindex, ctx, proc, params, func):
        self.putCmd(CallProcCmd(conindex, ctx, proc, params, func))
        